package com.wst.system.util.jdbc;

import com.alibaba.fastjson2.JSONObject;

import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SqlBuilder {

    public static void main(String[] args) throws IOException, SQLException {
        SqlBuilder sqlBuilder=new SqlBuilder();
        //  SqlBuilder sqlBuilder = new SqlBuilder();
        sqlBuilder.select("*").from("tb_blog").leftJoin("table").on("tb_blog.id","table.id");
        String s="1' or 1=1; -- ";
        sqlBuilder.and("click_num").eq(s);
        sqlBuilder.and("test").like("test123");
        sqlBuilder.and("test2").like(null);
        sqlBuilder.and().addMark("(").and("field1").eq(null);
        sqlBuilder.and("field2").eq(2);
        sqlBuilder.addMark(")");
        sqlBuilder.orderByDesc("createdate");
        sqlBuilder.page(1,10);
        //  sqlBuilder.and("blog_id").in(14,15,16);
        String sql=sqlBuilder.getSql();
        System.out.println(JSONObject.toJSONString(sqlBuilder.getObjects()));
        System.out.println(sql);
    }

    private final List<Object> objects;

    private final List<String> tableFields;

    private final StringBuilder selectBuilder;

    private String tableName;

    private final StringBuilder conditionBuilder;

    private final StringBuilder orderByBuilder;

    private final StringBuilder pageBuilder;

    private Boolean isWhereUsed;

    private Boolean conditionEmpty;

    private Boolean isAnd;

    private Boolean isOr;

    private String tableField;

    public SqlBuilder select(String tableField){
        this.tableFields.add(tableField);
        return this;
    }

    public SqlBuilder from(String tableName){
        this.tableName = tableName;
        return this;
    }

    public SqlBuilder page(Integer pageNum, Integer pageSize){
        pageNum=(pageNum-1) * pageSize;
        this.objects.add(pageNum);
        this.objects.add(pageSize);
        pageBuilder.append(" limit ? ,? ");
        return this;
    }

    public SqlBuilder() {
        selectBuilder=new StringBuilder();
        tableFields=new ArrayList<>();
        objects=new ArrayList<>();
        conditionBuilder=new StringBuilder();
        this.isWhereUsed=true;
        this.orderByBuilder=new StringBuilder();
        this.pageBuilder=new StringBuilder();
        this.conditionEmpty=false;
    }

    public SqlBuilder and(String tableField){
        this.tableField=tableField;
        this.isAnd = true;
        this.isOr=false;
        return this;
    }

    public SqlBuilder and(){
        appendCondition("and","");
        return this;
    }

    public SqlBuilder or(String tableField){
        this.tableField=tableField;
        this.isAnd = false;
        this.isOr=true;
        return this;
    }

    public SqlBuilder or(){
        appendCondition("or","");
        return this;
    }

    public SqlBuilder leftJoin(String tableName){
        conditionBuilder.append(" LEFT JOIN ").append(tableName);
        return this;
    }

    public SqlBuilder rightJoin(String tableName){
        conditionBuilder.append(" RIGHT JOIN ").append(tableName);
        return this;
    }

    public SqlBuilder Join(String tableName){
        conditionBuilder.append(" JOIN ").append(tableName);
        return this;
    }

    public SqlBuilder on(String associationField1, String associationField2){
        conditionBuilder.append(" ON ").append(associationField1).append("=").append(associationField2);
        return this;
    }

    private SqlBuilder appendCondition(){
        if(isAnd){
            appendCondition("and",this.tableField);
            this.isAnd=false;
        }
        if(isOr){
            appendCondition("or",this.tableField);
            this.isOr=false;
        }
        return this;
    }

    public SqlBuilder inSql(String sql){
        if(sql!=null && !"".equals(sql)){
            this.appendCondition();
            conditionBuilder.append(" in(").append(sql).append(")");
        }
        return this;
    }

    public SqlBuilder in(Object ... args){
        if(args!=null && args.length > 0){
            this.appendCondition();
            conditionBuilder.append(" in(");
            int size = args.length;
            for (int i = 0; i < args.length; i++) {
                Object arg=args[i];
                if(i==size-1){
                    conditionBuilder.append("?");
                }else {
                    conditionBuilder.append("?,");
                }
                this.objects.add(arg);
            }
            conditionBuilder.append(")");
        }
        return this;
    }

    public SqlBuilder addMark(String field){
        if(field.contains("(")){
            this.conditionEmpty=true;
        }
        if(field.contains(")")){
            this.conditionEmpty=false;
        }
        conditionBuilder.append(" ").append(field);
        return this;
    }

    public SqlBuilder orderByDesc(String ... fields){
        if(fields!=null && fields.length>0){
            this.orderByBuilder.append(" order by ");
            for (String field : fields) {
                this.orderByBuilder.append(field).append(",");
            }
            this.orderByBuilder.deleteCharAt(this.orderByBuilder.length()-1);
            this.orderByBuilder.append(" desc");
        }

        return this;
    }

    public SqlBuilder orderByAsc(String ... fields){
        if(fields!=null && fields.length>0){
            this.orderByBuilder.append(" order by ");
            for (String field : fields) {
                this.orderByBuilder.append(field).append(",");
            }
            this.orderByBuilder.deleteCharAt(this.orderByBuilder.length()-1);
            this.orderByBuilder.append(" asc");
        }
        return this;
    }

    public SqlBuilder appendCondition(String conditon, String tableField){
        if(this.isWhereUsed){
            //判断是否添加过where
            conditionBuilder.append(" where ").append(tableField);
            this.isWhereUsed=false;
        }else {
            if(this.conditionEmpty){
                //有先前面需要拼接条件and 和 or会报错，所以需要此设置
                this.conditionEmpty=false;
                conditionBuilder.append(" ").append(tableField);
            }else {
                conditionBuilder.append(" ").append(conditon).append(" ").append(tableField);
            }
        }
        return this;
    }

    public SqlBuilder eq(Object val){
        if(val!=null){
            this.appendCondition();
            conditionBuilder.append(" = ").append("?");
            this.objects.add(val);
        }
        return this;
    }

    public SqlBuilder like(Object val){
        if (val != null) {
            this.appendCondition();
            conditionBuilder.append(" like concat('%',?,'%') ");
            this.objects.add(val);
        }
        return this;
    }


    public SqlBuilder leftLike(Object val){
        if (val != null) {
            this.appendCondition();
            conditionBuilder.append(" like concat('%',?) ");
            this.objects.add(val);
        }
        return this;
    }

    public SqlBuilder rightLike(Object val){
        if (val != null) {
            this.appendCondition();
            conditionBuilder.append(" like concat(?,'%') ");
            this.objects.add(val);
        }
        return this;
    }

    /**
     * 小于查询
     *  <
     * @param val
     * @return
     */
    public SqlBuilder lt(Object val){
        if(val != null){
            this.appendCondition();
            this.conditionBuilder.append(" < ? ");
            this.objects.add(val);
        }
        return this;
    }


    /**
     *
     * 小于等于查询
     * <=
     * @param val
     * @return
     */
    public SqlBuilder le(Object val){
        if(val != null){
            this.appendCondition();
            this.conditionBuilder.append(" <= ? ");
            this.objects.add(val);
        }
        return this;
    }

    /**
     * 大于查询
     * @param val
     * @return
     */
    public SqlBuilder gt(Object val){
        if(val != null){
            this.appendCondition();
            this.conditionBuilder.append(" > ? ");
            this.objects.add(val);
        }
        return this;
    }


    /**
     * 大于等于查询
     * @param val
     * @return
     */
    public SqlBuilder ge(Object val){
        if(val != null){
            this.appendCondition();
            this.conditionBuilder.append(" >= ? ");
            this.objects.add(val);
        }
        return this;
    }



    public SqlBuilder isNotNull(){
        this.appendCondition();
        this.conditionBuilder.append(" IS NOT NULL");
        return this;
    }

    public SqlBuilder isNull(){
        this.appendCondition();
        this.conditionBuilder.append(" IS  NULL");
        return this;
    }


    public String getSql(){
        selectBuilder.append("select ");
        if(this.tableFields.size()>0){
            for (int i = 0; i < this.tableFields.size(); i++) {
                if(i==this.tableFields.size() - 1){
                    selectBuilder.append(tableFields.get(i));
                }else {
                    selectBuilder.append(tableFields.get(i)).append(",");
                }
            }
        }else {
            selectBuilder.append(" * ");
        }
        selectBuilder.append(" from ").append(tableName);
        selectBuilder.append(conditionBuilder.toString())
                .append(pageBuilder.toString())
                .append(orderByBuilder.toString());
        return selectBuilder.toString();
    }

    public String getCountSql(){
        String countString = "select count(*) ";
        return countString +" from "+tableName+conditionBuilder.toString();
    }

    public Object[] getObjects(){
        return this.objects.toArray();
    }
}
